﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetDeliverClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetDeliverClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetDeliverClass] 
        @UserName nvarchar(255),
        @RightCode int =1
/*
=========================================================
功能:    得到@UserName拥有@RightCode权利的类
参数:
备注:    好象可以删除
=========================================================
*/
AS

declare @Staff_id int

Set NoCount On
set @staff_id=0

--取得用户的ID
select @staff_id = staff_id from uds_staff where staff_name = @username

--创建临时类表
create table #class (obj_id int)

--把所有可显示的类ID收集起来
--act_id=1为显示权
if @staff_id>0
begin
    insert into #class 
    select team_id from UDS_Assign_Rule a,uds_proc b,uds_activity c where a.Position_id in (select Position_id from uds_staff where staff_id = @staff_id) and c.proc_id = b.proc_id and a.act_id = c.act_id and based_on =1 and b.proc_id= @RightCode
    insert into #class 
    select team_id from UDS_Assign_Rule a,uds_proc b,uds_activity c where a.team_id in (select team_id from uds_staff_in_team where staff_id = @staff_id) and c.proc_id = b.proc_id and a.act_id = c.act_id and based_on =2 and b.proc_id = @RightCode
    insert into #class 
    select team_id from UDS_Assign_Rule a,uds_proc b,uds_activity c where a.role_id in (select role_id from uds_staff_in_role where staff_id = @staff_id) and c.proc_id = b.proc_id and a.act_id = c.act_id and based_on =3 and b.proc_id =@RightCode
    insert into #class 
    select team_id from UDS_Assign_Rule a,uds_proc b,uds_activity c where a.staff_id = @staff_id and c.proc_id = b.proc_id and a.act_id = c.act_id and based_on =4 and b.proc_id =@RightCode
end 

if exists(select 1 from #class where obj_id =0) 
begin
    select * from uds_class where classtype<=1
    print '全局'
    return 
end

--得到拥有的类信息
Select * From uds_class where classid in (select obj_id from  #class) and classtype <=1 

Set NoCount Off